package com.vma.shoeSystem.controller;

import com.jfinal.core.Controller;
import com.vma.shoes.bizModel.model.OrderItem;
import com.vma.shoes.bizModel.model.OrderList;
import com.vma.shoes.bizModel.model.OrderOffer;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import jxl.write.Number;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;

/**
 * Created by chenzui on 2017/5/23.
 */
public class ExcelController extends Controller {
    public void out(){
        try {
            //获得输出流，该输出流的输出介质是客户端浏览器
                HttpServletResponse response = getResponse();
            OutputStream output=response.getOutputStream();

            response.reset();

            response.setHeader("Content-disposition","attachment;           filename=temp.xls");

            response.setContentType("application/msexcel");

            //创建可写入的Excel工作薄，且内容将写入到输出流，并通过输出流输出给客户端浏览

            WritableWorkbook wk= Workbook.createWorkbook(output);


///创建可写入的Excel工作表

            WritableSheet sheet=wk.createSheet("未发货订单", 0);

//把单元格（column, row）到单元格（column1, row1）进行合并。

//mergeCells(column, row, column1, row1);

            sheet.mergeCells(0,0, 8,0);//单元格合并方法

//创建WritableFont 字体对象，参数依次表示黑体、字号12、粗体、非斜体、不带下划线、亮蓝色

            WritableFont titleFont=new WritableFont(WritableFont.createFont("黑体"),12,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE, Colour.LIGHT_BLUE);

//创建WritableCellFormat对象，将该对象应用于单元格从而设置单元格的样式

            WritableCellFormat titleFormat=new WritableCellFormat();

//设置字体格式

//            titleFormat.setFont(titleFont);

//设置文本水平居中对齐

            titleFormat.setAlignment(Alignment.CENTRE);

//设置文本垂直居中对齐

            titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

//设置背景颜色

            titleFormat.setBackground(Colour.GRAY_25);

//设置自动换行

            titleFormat.setWrap(true);

//添加Label对象，参数依次表示在第一列，第一行，内容，使用的格式

            Label lab_00=new Label(0,0,"未发货订单一览表",titleFormat);

//将定义好的Label对象添加到工作表上，这样工作表的第一列第一行的内容为‘学员考试成绩一览表’并应用了titleFormat定义的样式

            sheet.addCell(lab_00);

            WritableCellFormat cloumnTitleFormat=new WritableCellFormat();

//            cloumnTitleFormat.setFont(new WritableFont(WritableFont.createFont("宋体"),10,WritableFont.BOLD,false));

            cloumnTitleFormat.setAlignment(Alignment.CENTRE);

            Label lab_01=new Label(0,1,"订单号",cloumnTitleFormat);

            Label lab_11=new Label(1,1,"联系人",cloumnTitleFormat);

            Label lab_21=new Label(2,1,"联系电话",cloumnTitleFormat);

            Label lab_31=new Label(3,1,"地址",cloumnTitleFormat);

            Label lab_41=new Label(4,1,"订单描述",cloumnTitleFormat);

            Label lab_51=new Label(5,1,"金额",cloumnTitleFormat);

            Label lab_61=new Label(6,1,"订单备注",cloumnTitleFormat);

            Label lab_71=new Label(7,1,"快递公司",cloumnTitleFormat);

            Label lab_81=new Label(8,1,"快递单号",cloumnTitleFormat);

            sheet.addCell(lab_01);

            sheet.addCell(lab_11);

            sheet.addCell(lab_21);

            sheet.addCell(lab_31);

            sheet.addCell(lab_41);

            sheet.addCell(lab_51);

            sheet.addCell(lab_61);

            sheet.addCell(lab_71);

            sheet.addCell(lab_81);


            List<OrderList> orderLists = OrderList.dao.find("SELECT o.id,o.orderNbr,o.remark,a.phone,a.reciver_name,o.total_cash,a.location , a.detail FROM orderList o,address a\n" +
                    "WHERE o.address_id = a.id\n" +
                    "AND o.status = 2");
            int index = 2;
            for (OrderList orderList:orderLists){
                String orderdesc = "";
                List<OrderOffer> offers = OrderOffer.dao.find("SELECT oo.id,o.offer_name,oo.offer_price,oo.offer_cnt FROM order_offer oo,offer o WHERE\n" +
                        "  oo.order_id = ?\n" +
                        "AND oo.offer_id = o.id;",orderList.getId());
                for (OrderOffer orderOffer:offers){
                    orderdesc += orderOffer.getStr("offer_name")+"-单价"+orderOffer.getOfferPrice()+"元"+"-"+orderOffer.get("offer_cnt")+"双";
                    List<OrderItem> items = OrderItem.dao.find("SELECT csi.spec_value color,lsi.spec_value length FROM order_item oi,spec_item csi,spec_item lsi WHERE order_offer_id = ?\n" +
                            "AND oi.color_id = csi.id AND oi.length_id = lsi.id",orderOffer.getId());
                    for (OrderItem oi:items){
                        orderdesc += oi.getStr("color")+"-"+oi.getStr("length");
                    }
                }
                orderList.put("desc",orderdesc);
                sheet.addCell(new Label(0,index,orderList.getOrderNbr()));
                sheet.addCell(new Label(1,index,orderList.getStr("reciver_name")));
                sheet.addCell(new Label(2,index,orderList.getStr("phone")));
                sheet.addCell(new Label(3,index,orderList.getStr("location")+orderList.getStr("detail")));
                sheet.addCell(new Label(4,index,orderdesc));
                sheet.addCell(new Label(5,index,orderList.getFloat("total_cash").toString()));
                sheet.addCell(new Label(6,index,orderList.getStr("remark")));
                sheet.addCell(new Label(7,index,""));
                sheet.addCell(new Label(8,index,""));

                index++;
            }

//            sheet.addCell(new Label(0,2,"李明"));
//
//            sheet.addCell(new Label(1,2,"As178"));
//
////定义数字格式
//
//            NumberFormat nf=new NumberFormat("0.00");
//
//            WritableCellFormat wcf=new WritableCellFormat(nf);
//
////类似于Label对象，区别Label表示文本数据，Number表示数值型数据
//
//            Number numlab_22=new Number(2,2,78,wcf);
//
//            sheet.addCell(numlab_22);
//
//            sheet.addCell(new Number(3,2,87,new WritableCellFormat(new NumberFormat("#.##") )));
//
////定义日期格式
//
//            DateFormat df=new DateFormat("yyyy-MM-dd hh:mm:ss");
//
////创建WritableCellFormat对象
//
//            WritableCellFormat datewcf=new WritableCellFormat(df);
//
////类似于Label对象，区别Label表示文本数据，DateTime表示日期型数据
//
//            DateTime dtLab_42=new DateTime(4,2,new Date(),datewcf);
//
//            sheet.addCell(dtLab_42);

//将定义的工作表输出到之前指定的介质中（这里是客户端浏览器）

            wk.write();

//操作完成时，关闭对象，释放占用的内存空间

            wk.close();
        }catch (Exception e){
            e.printStackTrace();
        }

    }
}
